package com.flong.modules.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.flong.modules.AppException;
import com.flong.modules.BaseService;
import com.flong.modules.model.oracle.Views;
import com.flong.modules.utils.DBHelper;
import com.flong.modules.utils.ObjectUtil;
import com.flong.modules.utils.PropertiesHelper;

/**
 * @Description	视图..
 * @ClassName	ViewService
 * @Date		2017年2月23日 下午3:52:24
 * @Author		liangjilong
 * @Copyright (c) All Right Reserved , 2017.
 */
@SuppressWarnings("all")
public class ViewService extends BaseService{

	private static String  VIEWS_FIELD_SQL = "owner, view_name, text_length, text, type_text_length, type_text, oid_text_length, oid_text, view_type_owner, view_type, superview_name, editioning_view, read_only";
	
	/**
	 * @Description 获取所有数据库序列
	 * @Author		liangjilong
	 * @Date		2017年2月23日 下午3:57:42
	 * @return
	 * @throws Exception 		参数
	 * @return 		List<String> 返回类型
	 */
	public static List<Views> getViews() throws Exception {
		conn = DBHelper.getInstance().getConnection(); //链接数据库
		String dialect = PropertiesHelper.get("jdbc.dialect");//获取方言
		if(ObjectUtil.isNotEmpty(dialect) && "ORACLE".equalsIgnoreCase(dialect)){
			sql = " select "+VIEWS_FIELD_SQL+" from all_views ";//
		}else if(ObjectUtil.isNotEmpty(dialect) && "SQLSERVER".equalsIgnoreCase(dialect)){
			//sql = " select * from views ";//@ TODO
		}else if(ObjectUtil.isNotEmpty(dialect) && "MYSQL".equalsIgnoreCase(dialect)){
			sql = " select * from views ";//
		} else{
			throw new AppException(1,"方言配置有误，目前只支持Oracle");
		}
		List<Views> convertToEntitys = retViewList(sql, conn);
		return convertToEntitys;
	}
	
	private static List<Views> retViewList(String sql, Connection conn)  {
		List<Views> convertToEntitys = null;
		try {
			psmt = conn.prepareStatement(sql);
			ResultSet rs = psmt.executeQuery();
			convertToEntitys = DBHelper.convertToEntitys(rs, Views.class);
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBHelper.release(conn, psmt, rs);
		}
		return convertToEntitys;
	}
	
	/**
	 * @Description  
	 * @Author		liangjilong
	 * @Date		2017年2月23日 下午4:25:08
	 * @param viewName
	 * @throws Exception 		参数
	 * @return 		List<String> 返回类型
	 */
	public static List<Views> getViewByName(String viewName) throws Exception {
		conn = DBHelper.getInstance().getConnection(); //链接数据库
		String dialect = PropertiesHelper.get("jdbc.dialect");//获取方言
		if(ObjectUtil.isNotEmpty(dialect) && "ORACLE".equalsIgnoreCase(dialect)){
			sql = " select "+VIEWS_FIELD_SQL+" from all_views where upper(view_name)=upper('"+viewName+"')";//
		}else if(ObjectUtil.isNotEmpty(dialect) && "SQLSERVER".equalsIgnoreCase(dialect)){
			//sql = " select * from views ";//@ TODO
		}else if(ObjectUtil.isNotEmpty(dialect) && "MYSQL".equalsIgnoreCase(dialect)){
			sql = " select * from views ";//@ TODO
		} else{
			throw new AppException(1,"方言配置有误，目前只支持Oracle");
		}
		List<Views> convertToEntitys = retViewList(sql, conn);
		return convertToEntitys;
	}

	/**
	 * @Description 根据表和列进行获取条数
	 * @Author		liangjilong
	 * @Date		2017年2月23日 下午3:47:24
	 * @param viewName
	 * @param column
	 * @return
	 * @throws Exception 		参数
	 * @return 		int 返回类型
	 */
	public static int getCountViewName(String viewName) throws Exception{
		int result = -1;
		conn = DBHelper.getInstance().getConnection(); //链接数据库
		if(ObjectUtil.isNotEmpty(dialect) && "ORACLE".equalsIgnoreCase(dialect)){
			sql = " select count(*) num from all_views where upper(view_name)=upper('"+viewName+"')";//
		}else if(ObjectUtil.isNotEmpty(dialect) && "SQLSERVER".equalsIgnoreCase(dialect)){
			//@Dcript TODO 
		}else if(ObjectUtil.isNotEmpty(dialect) && "MYSQL".equalsIgnoreCase(dialect)){
			//@Dcript TODO 
		}else{
			throw new AppException(1,"方言配置有误，目前只支持MYSQL和Oracle");
		}
		return getNumFieldValue(result, sql);
	}
	
	/**
	 * @Description 根据视图名称和列名称进行查询是否,返回总条数
	 * @Author		liangjilong
	 * @Date		2017年3月2日 下午5:02:28
	 * @param viewName
	 * @param columnName
	 * @return
	 * @throws Exception 		参数
	 * @return 		int 返回类型
	 */
	public static int getCountViewNameAndColumnName(String viewName,String columnName) throws Exception{
		int result = -1;
		conn = DBHelper.getInstance().getConnection(); //链接数据库
		String dialect = PropertiesHelper.get("jdbc.dialect");//获取方言
		if(ObjectUtil.isNotEmpty(dialect) && "ORACLE".equalsIgnoreCase(dialect)){
			sql = " select count(*) num from user_tab_cols where upper(table_name)=upper('"+viewName+"') and upper(column_name)=upper('"+columnName+"')";//
		}else if(ObjectUtil.isNotEmpty(dialect) && "SQLSERVER".equalsIgnoreCase(dialect)){
			//@Dcript TODO 
		}else if(ObjectUtil.isNotEmpty(dialect) && "MYSQL".equalsIgnoreCase(dialect)){
			//@Dcript TODO 
		}else{
			throw new AppException(1,"方言配置有误，目前只支持MYSQL和Oracle");
		}
		return getNumFieldValue(result, sql);
	}
	
	
	 
	
	
}
